clear all
set more off, perm
local mydir "`1'"
global Tables 	`mydir'/TS/Tables
global Work   	`mydir'/TS/DataWork
global Raw    	`mydir'/TS/DataRaw
global WRDS  	`mydir'/WRDS
/* ********************************************************************* */
* Get CRSP data on S&P 500 index
import sas using $WRDS/CRSP/msp500.sas7bdat, clear

gen datem=mofd(caldt)
format datem %tm

tsset datem

gen r=1+vwretd
gen rLTM= (L0.r)*(L1.r)*(L2.r)*(L3.r)*(L4.r)*(L5.r)*(L6.r)*(L7.r)*(L8.r)*(L9.r)*(L10.r)*(L11.r)

gen lnLvw1=ln(rLTM)
gen lnLvw2=lnLvw1+L12.lnLvw1
gen lnLvw3=lnLvw2+L24.lnLvw1
gen lnLvw4=lnLvw3+L36.lnLvw1
gen lnLvw5=lnLvw4+L48.lnLvw1

gen lnvw1=ln(F12.rLTM)
gen lnvw2=lnvw1+F12.lnvw1
gen lnvw3=lnvw2+F24.lnvw1
gen lnvw4=lnvw3+F36.lnvw1
gen lnvw5=lnvw4+F48.lnvw1

keep caldt datem ln*
/* ***************************** */
save $Work/crsp, replace
/* ********************************************************************* */
/* Get Shiller's data on earnings and dividends	(http://www.econ.yale.edu/~shiller/data.htm) 			 */
/* ********************************************************************* */
import excel using $Raw/shiller_updated.xls, sheet("formatted") firstrow clear
drop Fraction
keep if Date!=. 

foreach var of varlist RealDiv* RealEar* CAPE CAPD RCAE RCAD D E {
	destring `var', replace
}

*RCAE (real cyclically adjusted EPS) and RCAD (real cyclically adjusted DPS) are in real units; need to put them in nominal units
gen deflator=D / RealDividend
gen CAE=RCAE * deflator
gen CAD=RCAD * deflator

* generate date that will merge with my data 
* compute date in monthly format using mofd function
gen yr=int(Date)
gen mo=100*(Date-yr)

generate dateshiller=mdy(mo+1,1,yr  )-1 if mo<=11
replace  dateshiller=mdy(1   ,1,yr+1)-1	if mo==12
format dateshiller %td

gen datem=mofd(dateshiller)
format datem %tm
tsset datem

*compute 10-year moving average of earnings
gen double cae=L120.E
forvalues x=119(-1)1 {
	replace cae=cae+L`x'.E
}
replace cae=cae/120
* ************************** 
* Delete?:  Sanity check -- Use Shiller's data to compute returns
gen ret=(P+D)/L12.P
gen lnrsh1=ln(F12.ret)
gen lnrsh2=ln(F24.ret)+lnrsh1
gen lnrsh3=ln(F36.ret)+lnrsh2
gen lnrsh4=ln(F48.ret)+lnrsh3
gen lnrsh5=ln(F60.ret)+lnrsh4

gen CAEL12=L12.CAE
gen CAEL24=L24.CAE
gen CAEL36=L36.CAE
gen CAEL48=L48.CAE

forvalues x=1(1)5 {
local j=12*`x'
	gen  g`x'=(ln(F`j'.E)-ln(E))
	gen Lg`x'=(ln(E)-ln(L`j'.E))
}
gen Lgcae5=ln(cae)-ln(L60.cae)
pwcorr Lgcae5 Lg5

gen  g10=(ln(F10.E)-ln(F5.E))
order *, sequential
keep dateshiller datem *CAE* CAD CAPE* CAPD D E P lnrsh* g1-g5 g10 Lg1-Lg5 Real* CPI
* **********************************************
merge 1:1 datem using $Work/crsp
drop _merge
* DELETE:
merge 1:1 datem using /srv/scratch/credit_spread
drop _merge

order datem dateshiller date_FED
save $Work/shiller, replace
/* ********************************************************************* */
* Add data on CAPE and CAPD from Shiller
* /srv/scratch/cs04 generated by rlaporta\Aggregate\CampbellShiller\cs04.do
use /srv/update/cs04, clear
merge 1:1 datem using $Work/shiller
keep if _merge==3
drop _merge
* ******************************
replace fcastE5=fcastE4*(1+LTG/100) if fcastE5==.

forvalues x=6(1)10 {
	local j=`x'-1
	gen fcastE`x'=fcastE`j'*(1+LTG/100)  
}
save $Work/prgm01, replace
* *******************************	
